package xkp.lesson.dao;

import xkp.lesson.db.DBConnection;
import xkp.lesson.entity.UserInfoEntity;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 在jdbc中面临的问题
 * 1、代码非常繁琐  每一次都要连接数据库 关闭数据库 要new对象(后面会交给spring处理)
 * 2、代码其实没什么技术含量  无非增删改查 (使用mybatis替代)
 * 3、很容易出错           数据库中字段与对象中的属性匹配很容易出错 (使用mybatis替代)
 * 4、代码量很大   字段多的话，工作量大增 (使用mybatis替代)
 *
 * 能把能把上述所有操作简化到只需写sql即可
 */
public class UserInfoDao implements BaseDao<UserInfoEntity>{
    public  List<UserInfoEntity> selectByPage(Integer pageNum,Integer pageSize) throws SQLException {
        List<UserInfoEntity> userInfoList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        //此处的sql应该换为调用存储过程
        String sql = "CALL pro_select_page(?,?)";
        CallableStatement statement = connection.prepareCall(sql);
        statement.setInt(1,pageNum);
        statement.setInt(2,pageSize);
        ResultSet set = statement.executeQuery();
        while(set.next()){

            Integer userId = set.getInt("user_ID");
            String userName = set.getString("user_name");
            String userPwd = set.getString("user_pwd");
            String nickName  = set.getString("nick_name");
            String userSex = set.getString("user_sex");
            Integer userAge = set.getInt("user_age");
            String phoneNumber  = set.getString("phone_number");
            String email  = set.getString("email");
            //java.sql.Date java.sql.Time
            //java.sql.DateTime
            Date createTime = set.getTimestamp("create_time");
            Date updateTime = set.getTimestamp("update_time");
            //Date
            //TimeStamp转Date
            Date date = new Date(createTime.getTime());
            //Date转TimeStamp
            Timestamp timestamp = new Timestamp(date.getTime());

            UserInfoEntity userInfo = new UserInfoEntity(userId, userName, userPwd,
                    nickName, userSex, userAge, phoneNumber, email,createTime,updateTime);
            userInfoList.add(userInfo);
        }
        set.close();
        statement.close();
        connection.close();
        return userInfoList;
    }



    //操作数据库都要获取连接，获取连接很麻烦，能不能封装成一个方法
    @Override
    public  List<UserInfoEntity> selectAll() throws SQLException {
        List<UserInfoEntity> userInfoList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        String sql = "select * from user_info";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet set = statement.executeQuery();
        while(set.next()){
            Integer userId = set.getInt("user_ID");
            String userName = set.getString("user_name");
            String userPwd = set.getString("user_pwd");
            String nickName  = set.getString("nick_name");
            String userSex = set.getString("user_sex");
            Integer userAge = set.getInt("user_age");
            String phoneNumber  = set.getString("phone_number");
            String email  = set.getString("email");
            UserInfoEntity userInfo = new UserInfoEntity(userId, userName, userPwd,
                    nickName, userSex, userAge, phoneNumber, email);
            userInfoList.add(userInfo);
        }
        set.close();
        statement.close();
        connection.close();
        return userInfoList;
    }


    public UserInfoEntity login(String userName,String userPwd) throws SQLException {
        UserInfoEntity userInfo = null;
        Connection connection = DBConnection.getConnection();
        String sql = "select * from user_info where user_name = ? and user_PWD = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userName);
        statement.setString(2,userPwd);

        ResultSet set = statement.executeQuery();
        while(set.next()){
            Integer userId = set.getInt("user_ID");
            String nickName  = set.getString("nick_name");
            String userSex = set.getString("user_sex");
            Integer userAge = set.getInt("user_age");
            String phoneNumber  = set.getString("phone_number");
            String email  = set.getString("email");
            userInfo = new UserInfoEntity(userId, userName, userPwd,
                    nickName, userSex, userAge, phoneNumber, email);
            break;
        }
        set.close();
        statement.close();
        connection.close();
        return userInfo;
    }

    @Override
    public  UserInfoEntity selectById(Integer userId) throws SQLException {
        UserInfoEntity userInfo = null;
        Connection connection = DBConnection.getConnection();
        String sql = "select * from user_info where user_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,userId);


        ResultSet set = statement.executeQuery();
        while(set.next()){
            //Integer userId = set.getInt("user_ID");
            String userName = set.getString("user_name");
            String userPwd = set.getString("user_pwd");
            String nickName  = set.getString("nick_name");
            String userSex = set.getString("user_sex");
            Integer userAge = set.getInt("user_age");
            String phoneNumber  = set.getString("phone_number");
            String email  = set.getString("email");
            userInfo = new UserInfoEntity(userId, userName, userPwd,
                    nickName, userSex, userAge, phoneNumber, email);
            break;
        }
        set.close();
        statement.close();
        connection.close();
        return userInfo;
    }

    @Override
    public boolean insert(UserInfoEntity entity) throws SQLException {
        return false;
    }

    public void batchInsert(List<UserInfoEntity> list) throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "insert into user_info (user_ID,user_name,user_pwd,nick_name,user_sex,user_age) " +
                "values (?,?,?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        for(UserInfoEntity entity:list){
            statement.setInt(1,entity.getUserId());
            statement.setString(2,entity.getUserName());
            statement.setString(3,entity.getUserPwd());
            statement.setString(4,entity.getNickName());
            statement.setString(5,entity.getUserSex());
            statement.setInt(6,entity.getUserAge());
            //把这些参数加入批处理
            statement.addBatch();
        }

        //执行批处理
        statement.executeBatch();
        connection.close();
    }

    public  boolean insert(Connection connection,UserInfoEntity entity) throws SQLException {
        boolean flag = false;
        //Connection connection = DBConnection.getConnection();
        String sql = "insert into user_info (user_name,user_pwd,nick_name,user_sex,user_age) " +
                "values (?,?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,entity.getUserName());
        statement.setString(2,entity.getUserPwd());
        statement.setString(3,entity.getNickName());
        statement.setString(4,entity.getUserSex());
        statement.setInt(5,entity.getUserAge());
        //此处执行的是更新  insert/delete/update都算更新动作
        //executeUpdate的返回值为影响的行数
        int count = statement.executeUpdate();
        if(count>0){
            flag = true;
        }
        statement.close();
        //connection.close();
        return flag;
    }

    @Override
    public boolean deleteById(Integer userId) throws SQLException {
        boolean flag = false;
        Connection connection = DBConnection.getConnection();
        String sql = "delete from user_info where user_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,userId);
        //此处执行的是更新  insert/delete/update都算更新动作
        //executeUpdate的返回值为影响的行数
        int count = statement.executeUpdate();
        if(count>0){
            flag = true;
        }
        statement.close();
        connection.close();
        return flag;
    }

    @Override
    public  boolean update(UserInfoEntity entity) throws SQLException {
        boolean flag = false;
        Connection connection = DBConnection.getConnection();
        String sql = "update user_info set user_name = ?,user_pwd = ?," +
                "nick_name = ?,user_sex = ?, user_age = ? where  user_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,entity.getUserName());
        statement.setString(2,entity.getUserPwd());
        statement.setString(3,entity.getNickName());
        statement.setString(4,entity.getUserSex());
        statement.setInt(5,entity.getUserAge());
        statement.setInt(6,entity.getUserId());
        //此处执行的是更新  insert/delete/update都算更新动作
        //executeUpdate的返回值为影响的行数
        int count = statement.executeUpdate();
        if(count>0){
            flag = true;
        }
        statement.close();
        connection.close();
        return flag;
    }

}
